﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using libDAL;

namespace libAddressBook.BUS
{
    class clsMainBUS
    {

      //  private string strTempConStr = @"Data Source=193.9.200.92;Initial Catalog=ESB_Enterprise;Integrated Security=True";
        private string strTempConStr = @"Data Source=193.9.200.92\SERVERARCHIE;Initial Catalog=ESB_Enterprise;User ID=abc;Password=1234";

        private struct Queries
        {
            public string get_QueryCompanyNew
            {
                get
                {
                    return "SELECT top(0) companyHistoryID, comid, oldcomid, portid, comregno, comname, comname2, addbookcode, induscode, website, addline1, addline2, addline3, addline4, areacode, " +
                               "citycode, stacode, poscode, coucode, discode, email, connotepre, insper, insperfra, insminamt, minchaamt, contact, telpre, telno, faxpre, faxno, conope, slclotime, " +
                               "selected, regcust, glno, inscover, remarks, slotbook, bolprefix, holdgrn, holdrea, anote, nbl, fclinscover, fclinsper, fclinsperfra, fclinsminamt, fclstampduty, fclgovtax, " +
                               "memo, fcltermday, customcode, salperson, faxattn, markup, mpercent, byshipmea, allowvariance, cashbill, svbyemail, svbyfax, acccode, accid, billage, billbr, " +
                               "createdby, createddate, lastmodifiedby, lastmodifieddate, vip, minfrtrate, userreportprint, reportprintdate, reportprintby, hakgerakandate, svconsignee, autovesselsc, " +
                               "emailvesselsc, custgradecode, IsExportShprDO, mobilepre, mobileno, comtype, branchID, DateTimeInserted, DateTimeUpdated, HostNameIPAddress, OnUse, " +
                               "OnUseBy, comidHQ,comDefault " +
                               "FROM mfcompany ";
                }
            }

            public string get_QueryCompanyDetails(string strCompanyID, string strCompanyType)
            {
                string strQuery = string.Empty;
                string strVerb = string.Empty;
                try
                {
                    if (strCompanyType == "H")
                    {
                        strVerb = "where substring(comidHQ,0,CHARINDEX('_', comidHQ))  =  " +
                                   "(select comid from mfcompany " +
                                   "where comid = '" + strCompanyID + "' and acccode = '" + strCompanyType + "') " +
                                   "order by  comname asc";
                    }
                    else if (strCompanyType == "B")
                    {
                        strVerb = " where substring(comidHQ,0,CHARINDEX('_', comidHQ))  =  " +
                                  " (select substring(comidHQ,0,CHARINDEX('_', comidHQ)) from mfcompany " +
                                  "where comid = '" + strCompanyID + "') " +
                                  "order by  comname asc ";
                    }
                    else
                    {
                        strVerb = " where ltrim(rtrim(comid)) = '" + strCompanyID + "' " +
                                   "order by comname asc ";
                    }



                    strQuery = "SELECT companyHistoryID, comid, oldcomid, portid, comregno, comname, comname2, addbookcode, induscode, website, addline1, addline2, addline3, addline4, areacode, " +
                               "citycode, stacode, poscode, coucode, discode, email, connotepre, insper, insperfra, insminamt, minchaamt, contact, telpre, telno, faxpre, faxno, conope, slclotime, " +
                               "selected, regcust, glno, inscover, remarks, slotbook, bolprefix, holdgrn, holdrea, anote, nbl, fclinscover, fclinsper, fclinsperfra, fclinsminamt, fclstampduty, fclgovtax, " +
                               "memo, fcltermday, customcode, salperson, faxattn, markup, mpercent, byshipmea, allowvariance, cashbill, svbyemail, svbyfax, acccode, accid, billage, billbr, " +
                               "createdby, createddate, lastmodifiedby, lastmodifieddate, vip, minfrtrate, userreportprint, reportprintdate, reportprintby, hakgerakandate, svconsignee, autovesselsc, " +
                               "emailvesselsc, custgradecode, IsExportShprDO, mobilepre, mobileno, comtype, branchID, DateTimeInserted, DateTimeUpdated, HostNameIPAddress, OnUse, " +
                               "OnUseBy, comidHQ,comDefault,comStatus " +
                               "FROM mfcompany " + strVerb;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + "clsMainBUS_Queries_get_QueryCompanyDetails"); 
                }
                return strQuery;
            }

            public string get_QueryCompanyHistory(string strCompanyID)
            {
                return "SELECT comidHQ, comid, oldcomid, portid, comregno, comname, comname2, addbookcode, induscode, website, addline1, addline2, addline3, addline4, areacode, citycode, stacode, " +
                       "poscode, coucode, discode, email, connotepre, insper, insperfra, insminamt, minchaamt, contact, telpre, telno, faxpre, faxno, conope, slclotime, selected, regcust, " +
                       "glno, inscover, remarks, slotbook, bolprefix, holdgrn, holdrea, anote, nbl, fclinscover, fclinsper, fclinsperfra, fclinsminamt, fclstampduty, fclgovtax, memo, fcltermday, " +
                       "customcode, salperson, faxattn, markup, mpercent, byshipmea, allowvariance, cashbill, svbyemail, svbyfax, acccode, accid, billage, billbr, createdby, createddate, " +
                       "lastmodifiedby, lastmodifieddate, vip, minfrtrate, userreportprint, reportprintdate, reportprintby, hakgerakandate, svconsignee, autovesselsc, emailvesselsc, " +
                       "custgradecode, IsExportShprDO, mobilepre, mobileno, comtype, branchID, DateTimeInserted, DateTimeUpdated, HostNameIPAddress, OnUse, OnUseBy   " +
                       "FROM mfcompanyHistory " +
                       "where comid = '" + strCompanyID + "'";
            }
        }

        private struct CompanyElements
        {
          

        }

        protected struct CompanyDetailsHolder
        {
            public DataSet dsCompanyAddressDetails;
        
        }

        protected cls_DAL _DAL;
        protected CompanyDetailsHolder DataHolder = new CompanyDetailsHolder();

        public cls_DAL get_DAL
        {
            get
            {
                return _DAL;
            }
        }

        public clsMainBUS()
        {
            _DAL = new cls_DAL(strTempConStr);
            DataHolder.dsCompanyAddressDetails = new DataSet(); 
        }

        private bool set_UpdateCompanyDetailsMain(DataRow drUpdate)
        {
            string srx;
            bool bolStatus = false;
            try
            {
                string strCompanyID = drUpdate["comid"].ToString().Trim();
                DataRow[] drUpdateM = this.DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Select("comid = '" + strCompanyID + "'");
                string sr = drUpdateM[0]["comDefault"].ToString();
                string strx = drUpdate["comDefault"].ToString();
                if (drUpdateM.Length > 0)
                {
                    for (int intCol = 0; intCol < this.DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Columns.Count; intCol++)
                    {
                        drUpdateM[0][this.DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Columns[intCol].ColumnName] = drUpdate[this.DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Columns[intCol].ColumnName]; 
                    }
                    bolStatus = true;
                }
                 srx = drUpdateM[0]["comDefault"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_UpdateCompanyDetailsMain"); 
            }
            
            return bolStatus;
        }

        public bool set_UpdateMainAddressBook()
        {
            return _DAL.set_Bool_updateBatchNoAcceptChanges(ref this.DataHolder.dsCompanyAddressDetails, "CompanyDetailsMain-0");
        }

        public bool set_UpdateMain(DataRow drHolder, string strCompanyID)
        {
            bool bolStatus = false;
            try
            {
                if (set_UpdateCompanyDetailsMain(drHolder) == true)
                {
                    //bolStatus = _DAL.set_Bool_updateBatch(ref this.DataHolder.dsCompanyAddressDetails, "CompanyDetailsMain-0");
                    DataRow[] dr = this.DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Select("comid = '" + strCompanyID + "'");
                    DataTable dt = this.DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Clone();
                    
                    if (dr.Length > 0)
                    {
                        for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
                        {
                            dr[0][dt.Columns[intCol].ColumnName] = drHolder[dt.Columns[intCol].ColumnName];
                        }
                    }

                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_UpdateMain"); 
            }

            return bolStatus;
        }

        public DataTable get_AllCompany
        {
            get
            {
                return DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"];
            }
        }

        public DataRow[] get_CompanyDetails(string strCompanyID)
        {
            DataRow[] drDetails = { };
            try
            {
                drDetails = DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Select("comid = '" + strCompanyID.Trim() + "'");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CompanyDetails");
            }
            return drDetails;
        }

        public void set_InitializeAddressDetails()
        {
            DataHolder.dsCompanyAddressDetails = new DataSet();
        }

        public bool set_LoadCompanyDetails(string strCompanyID, string strCompanyType)
        {
            bool bolStatus = false;
            try
            {
                DataHolder.dsCompanyAddressDetails = new DataSet();
                Queries getQueryL = new Queries();
                _DAL.set_DataWCardinality_ds(getQueryL.get_QueryCompanyDetails(strCompanyID, strCompanyType),
                                            ref DataHolder.dsCompanyAddressDetails,
                                            "CompanyDetailsMain-0");
               bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_LoadCompanyDetails"); 
            }
            return bolStatus;
        }

        public bool set_RejectChanges()
        {
            bool bolStatus = false;
            try
            {
                DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].RejectChanges();
                if (_DAL.Update_batch_ds(ref DataHolder.dsCompanyAddressDetails, "CompanyDetailsMain-0") == true)
                {
                    bolStatus = true;
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_RejectChanges"); 
            }
            return bolStatus;
        }

        public bool set_UpdateExistingItems(DataTable dtNew)
        {
            bool bolStatus = false;
            try
            {

                DataRow[] drUpdate = dtNew.Select();
                DataRow[] drCheck = { };
                ///DataRow drInsert;

                for (int intRow = 0; intRow < drUpdate.Length; intRow++)
                {
                    //gets the existing historyID vs. history id from the interface
                    drCheck = DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Select("companyHistoryID = '" + drUpdate[intRow]["companyHistoryID"] + "'");
                    if (drCheck.Length > 0)
                    {
                        for (int intCol = 0; intCol < DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Columns.Count; intCol++)
                        {
                            //updates each value 
                            drCheck[0][intCol] = drUpdate[intRow][intCol];
                        }
                    }
                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + ""); 
            }
            return bolStatus;
        }

        public bool set_InsertNewItems(DataTable dtNew)
        {
            bool bolStatus = false;
            try
            {

                DataRow[] drUpdate = dtNew.Select();
                DataRow[] drCheck = { };

                DataRow drInsert;
                //checks for newly added company details
                for (int intRow = 0; intRow < drUpdate.Length; intRow++)
                {
                    drCheck = DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Select("comid = '" + drUpdate[intRow]["comid"] + "'");
                    if (drCheck.Length == 0)
                    {
                        drInsert = DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].NewRow();
                        // drUpdate = dtNew.Select("comid = '" + drUpdate[intRow]["comid"] + "'");
                        if (drUpdate.Length > 0)
                        {
                            for (int intCol = 0; intCol < DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Columns.Count; intCol++)
                            {
                                drInsert[intCol] = drUpdate[intRow][intCol];
                            }
                            DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Rows.Add(drInsert);
                        }
                    }
                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_InsertNewItems"); 
            }

            return bolStatus;
        }

        public bool set_UpdateAddressBookdt(DataTable dtNew)
        {
            bool bolStatus = false;
            try
            {
                bool bolUpdate = false;
                bool bolInsert = false;

                bolUpdate = set_UpdateExistingItems(dtNew);
                bolInsert = set_InsertNewItems(dtNew);

                if (bolUpdate == true && bolInsert == true)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_UpdateAddressBookdt"); 
            }

            return bolStatus;
        }

        public bool set_NewCompanyMain()
        {
            bool bolStatus = false;
            try
            {
                 Queries getQueryL = new Queries();
                 DataHolder.dsCompanyAddressDetails = new DataSet();
                 bolStatus = _DAL.set_DataWCardinality_ds(getQueryL.get_QueryCompanyNew, 
                                                          ref DataHolder.dsCompanyAddressDetails, 
                                                          "CompanyDetailsMain-0");
            }
            catch (Exception ex)
            { 
            }

            return bolStatus;
            //throw new NotImplementedException();
        }

        public bool get_CheckDatastRows()
        {
            bool bolStatus = false;
            try
            {
                int intTables = DataHolder.dsCompanyAddressDetails.Tables.Count;
                if (intTables <= 0)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckDatastRows"); 
            }
            return bolStatus;
        }

        public bool set_AddNewCompany(DataRow drAdd)
        {
            bool bolStatus = false;
            DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Rows.Add(drAdd);
            bolStatus = true;
            return bolStatus;
            //throw new NotImplementedException();
        }
        //Checks if
        public bool get_checkRecordCountHQExists()
        {
            bool bolStatus = false;
            try
            {
                if (DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"] != null)
                {
                    DataRow[] drCheck = DataHolder.dsCompanyAddressDetails.Tables["CompanyDetailsMain-0"].Select("comDefault = 1");
                  if (drCheck.Length == 0)
                    {
                        bolStatus = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_checkHQExists"); 
            }

            return bolStatus;
        }
    }
}
